﻿USE [ReTS_Db]
GO 

PRINT ''
PRINT 'Creating stored procedures for User table...'

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SelectUserById]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_SelectUserById]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_SelectUserById]
    @User_Id int
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM [User] WHERE User_Id = @User_Id

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_InsertUser]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_InsertUser]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_InsertUser]
    @User_Name nvarchar(25),
    @Password nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [User] 
	(
	User_Name, 
	Password
	)
VALUES 
	(
	@User_Name, 
	@Password
	)

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_UpdateUser]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_UpdateUser]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_UpdateUser]
    @User_Id int,
    @User_Name nvarchar(25),
    @Password nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [User] 
SET 
	User_Name = @User_Name, 
	Password = @Password
WHERE User_Id = @User_Id

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DeleteUser]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_DeleteUser]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_DeleteUser]
    @User_Id int
AS
BEGIN
SET NOCOUNT ON;

DELETE
FROM [User] 
WHERE User_Id = @User_Id

END
'
GO 